﻿USE [HanhudDB]
GO
/* PhotoCategories */

IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.PhotoCategories_CreatePhotoCategory') AND [type] IN (N'P')))
	DROP PROCEDURE dbo.PhotoCategories_CreatePhotoCategory
GO
CREATE PROCEDURE dbo.PhotoCategories_CreatePhotoCategory
	@ParentPhotoCategoryID	INT,
	@AddedDate				DATETIME,
	@Title					NVARCHAR(500),
	@Description			NVARCHAR(2000),
	@Published				BIT = 0
AS
BEGIN
	INSERT INTO PhotoCategories(ParentPhotoCategoryID, AddedDate, Title, [Description], Published)
	VALUES (@ParentPhotoCategoryID, @AddedDate, @Title, @Description, @Published)
	
	RETURN @@IDENTITY
END
GO

IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.PhotoCategories_UpdatePhotoCategory') AND [type] IN (N'P')))
	DROP PROCEDURE dbo.PhotoCategories_UpdatePhotoCategory
GO
CREATE PROCEDURE dbo.PhotoCategories_UpdatePhotoCategory
	@PhotoCategoryID		INT,
	@ParentPhotoCategoryID	INT,
	@Title					NVARCHAR(500),
	@Description			NVARCHAR(2000),
	@Published				BIT = 0
AS
BEGIN
	UPDATE PhotoCategories SET
		ParentPhotoCategoryID	= @ParentPhotoCategoryID,
		Title					= @Title,
		[Description]			= @Description,
		Published				= @Published
	WHERE PhotoCategoryID = @PhotoCategoryID
END
GO

IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.PhotoCategories_DeletePhotoCategory') AND [type] IN (N'P')))
	DROP PROCEDURE dbo.PhotoCategories_DeletePhotoCategory
GO
CREATE PROCEDURE dbo.PhotoCategories_DeletePhotoCategory
	@PhotoCategoryID	INT,
	@Recursive			BIT = 1
AS
BEGIN
	DECLARE @ErrorCode INT
	SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0
    	
	iF(@Recursive = 0) BEGIN
		DECLARE @ParentPhotoCategoryID INT
		
		-- Lay danh muc cha
		SELECT @ParentPhotoCategoryID = ParentPhotoCategoryID
		FROM PhotoCategories
		WHERE PhotoCategoryID = @PhotoCategoryID
		
		-- Lay cac danh muc con cap 1
		;WITH ChildCategories(PhotoCategoryID)
		AS
		(
			SELECT PhotoCategoryID FROM PhotoCategories WHERE ParentPhotoCategoryID = @PhotoCategoryID	
		)
		
		-- Dua cac danh muc con nay thanh danh muc con cua danh muc cha
		UPDATE PhotoCategories SET
			ParentPhotoCategoryID = @ParentPhotoCategoryID
		WHERE PhotoCategoryID IN (SELECT PhotoCategoryID FROM ChildCategories)
		
		IF(@ErrorCode <> 0) BEGIN
			SET @ErrorCode = @@ERROR
			GOTO CleanUp
		END
		
		-- Xoa danh muc nay
		DELETE PhotoCategories WHERE PhotoCategoryID = @PhotoCategoryID
		
		IF(@ErrorCode <> 0) BEGIN
			SET @ErrorCode = @@ERROR
			GOTO CleanUp
		END
	END
	ELSE BEGIN
		-- Lay cac danh muc con cua danh muc can xoa
		;WITH AllChildCategories(IndexID, PhotoCategoryID)
		AS
		(
			-- Seed, danh muc con cap 1
			SELECT 1 AS IndexID, PhotoCategoryID FROM PhotoCategories WHERE ParentPhotoCategoryID = @PhotoCategoryID	
			
			UNION ALL
			
			-- Lay cac danh muc con cua danh muc con
			SELECT (apc.IndexID + 1) AS IndexID, pc.PhotoCategoryID
			FROM PhotoCategories pc INNER JOIN
				AllChildCategories apc ON pc.ParentPhotoCategoryID = apc.PhotoCategoryID
		)
		
		-- Xoa cac danh muc con theo chieu giam dan cua IndexID
		DELETE PhotoCategories
		WHERE PhotoCategoryID IN (SELECT PhotoCategoryID FROM AllChildCategories ORDER BY IndexID DESC)
		
		IF(@ErrorCode <> 0) BEGIN
			SET @ErrorCode = @@ERROR
			GOTO CleanUp
		END
		
		-- Xoa danh muc nay
		DELETE PhotoCategories WHERE PhotoCategoryID = @PhotoCategoryID
		
		IF(@ErrorCode <> 0) BEGIN
			SET @ErrorCode = @@ERROR
			GOTO CleanUp
		END
	END
	
	IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0
	
CleanUp:
	IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END
GO

